<?php
defined ( 'BASEPATH' ) or exit ( 'No direct script access allowed' );
class Orderdetail_model extends CI_Model {
	public function __construct() {
		parent::__construct ();
	}
	/*
	 * 根据订单详情ID返回订单详情信息
	 */
	public function load($id){//
		$this->db->where('id',$id);
		$query = $this->db->get('t_orderdetail');
		if ($row = $query->row_array()){
			return $row;
		}
		return array();
	}
	/*
	 * 更新订单详情信息
	 */
	public function update($id,$orderdetail){
		$this->db->where('id', $id);
		$this->db->update('t_orderdetail', $orderdetail);
		return $this->db->affected_rows()>0;
	}
	/*
	 * 删除订单详情信息
	 */
	public function delete($id){
		$this->db->where('id', $id);
		$this->db->delete('t_orderdetail');
		return $this->db->affected_rows()>0;
	}
	/*
	 * 保存订单详情信息
	 */
	public function create($orderdetail){
		$this->db->insert('t_orderdetail', $orderdetail);
		return $this->db->insert_id();
	}
	/*
	 * 根据订单ID得到订单详情
	 */
	public function find_orderdetails($orderid){
		$this->db->select('t_orderdetail.id,t_orderdetail.productid,t_product.name,t_orderdetail.amount,t_orderdetail.price,t_product.unit,t_product.description');
		$this->db->from('t_orderdetail');
		$this->db->where('t_orderdetail.orderid',$orderid);
		$this->db->join('t_product', 't_orderdetail.productid = t_product.id','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	public function find_supplierorderdetails($orderid){
		$this->db->select('s_orderdetail.id,s_product.name as name,s_orderdetail.amount,s_product.price as price,s_product.unit,s_product.description');
		$this->db->from('s_orderdetail');
		$this->db->where('s_orderdetail.orderid',$orderid);
		$this->db->join('s_product', 's_orderdetail.productid = s_product.id','left');
		$query = $this->db->get();
		return $query->result_array();
	}
	/*
	 * 根据日期得到要导出的订单详情(该方法用于导出采购单，需要+商品数量)
	 */
	public function find_export_orderdetails($date){//
		$sql="select t_product.id,t_product.name,amount,t_product.unit,t_product.price,t_product.description,t_product.type from t_orderdetail,t_product,t_category
				where t_orderdetail.productid=t_product.id and t_product.categoryid=t_category.id
				and t_orderdetail.orderid in (select t_order.id from t_order where t_order.state>=2 and t_order.del=0 and TO_DAYS(t_order.receive_time)-TO_DAYS('".$date."')=1)
				order by t_category.export,t_product.export,t_product.id";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	/*
	 * 根据日期和付款方式得到订单详情(该方法用于导出日报单)
	 */
	public function find_export_report($date,$payment){//
		$pay="";
		if($payment==1){
			$pay=" and payment_mode=2 ";
		}
		if($payment==2){
			$pay=" and (payment_mode=3 or payment_mode=4) ";
		}
		$sql="select t_product.id,t_product.name,sum(amount) as amount,t_product.unit,t_orderdetail.price,t_product.purprice  from t_orderdetail,t_product 
				where t_orderdetail.productid=t_product.id 
				and t_orderdetail.orderid in (select t_order.id from t_order where t_order.state>=2 ".$pay." and TO_DAYS(t_order.receive_time)-TO_DAYS('".$date."')=1)
				group by productid order by t_product.categoryid,t_product.export";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	/*
	 * 根据日期和大类得到订单详情(该方法用于导出全部日报单)
	 */
	public function find_export_reports($date,$category){//
		if($category==0){
			$cat=" and categoryid not in(13,14,17) ";
		}else{
			$cat=" and categoryid=".$category;
		}
		$sql="select t_product.id,t_product.name,amount,t_product.unit,t_orderdetail.price,t_product.purprice  from t_orderdetail,t_product 
				where t_orderdetail.productid=t_product.id ".$cat 
				." and t_orderdetail.orderid in (select t_order.id from t_order where t_order.state>=2  and TO_DAYS(t_order.receive_time)-TO_DAYS('".$date."')=1)
				order by t_product.export,t_product.id";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	/*
	 * 根据起始日期和大类得到订单详情(该方法用于导出销售表)
	 */
	public function find_export_sales($startdate,$enddate,$category){//
		if($category==0){
			$cat=" and categoryid not in(13,14,17) ";
		}else{
			$cat=" and categoryid=".$category;
		}
		$sql="select t_product.name,sum(amount) as amount,t_product.unit,t_orderdetail.price,t_product.purprice  from t_orderdetail,t_product 
				where t_orderdetail.productid=t_product.id ".$cat 
				." and t_orderdetail.orderid in (select t_order.id from t_order where t_order.state>=2  and t_order.receive_time>='".$startdate."' and  t_order.receive_time<='".$enddate."')
				group by productid order by t_product.export";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	/*
	 * 根据起始日期导出采购单(该方法用于导出采购统计表和导出销售表)
	 */
	public function find_export_orderdetail($startdate,$enddate){//
		$sql="select t_product.name,sum(amount) as amount,t_product.unit,t_orderdetail.price,t_product.purprice,t_product.description from t_orderdetail,t_product 
				where t_orderdetail.productid=t_product.id 
				and t_orderdetail.orderid in (select t_order.id from t_order where t_order.state>=2 and t_order.receive_time>='".$startdate."' and  t_order.receive_time<='".$enddate."')
				group by productid order by t_product.categoryid,t_product.export";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	/*
	 * 根据日期得到要导出的订单详情(该方法用于导出分拣表)
	 */
	public function find_export_sort($date){//
		$sql="select t_order.id,t_user.id as userid,t_user.shopname,t_product.id as productid,t_product.categoryid,t_product.name,amount,t_product.unit,t_product.description,t_product.type from t_orderdetail,t_product,t_order,t_user,t_category
				where t_orderdetail.productid=t_product.id and t_orderdetail.orderid=t_order.id and t_order.userid= t_user.id and t_product.categoryid=t_category.id
				and t_orderdetail.orderid in (select t_order.id from t_order where t_order.state>=2 and TO_DAYS(t_order.receive_time)-TO_DAYS('".$date."')=1)
				order by t_category.export,t_product.export,productid";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	/*
	 * 根据日期和用户得到要导出的订单详情(该方法用于导出分拣表)
	 */
	public function find_new_export_sort($date,$user){//
		$sql="select t_order.id,t_user.id as userid,t_user.shopname,t_product.id as productid,t_product.categoryid,t_product.name,amount,t_product.unit,t_product.description,t_product.type from t_orderdetail,t_product,t_order,t_user,t_category
				where t_orderdetail.productid=t_product.id and t_orderdetail.orderid=t_order.id and t_order.userid= t_user.id and t_product.categoryid=t_category.id
				and t_orderdetail.orderid in (select t_order.id from t_order where t_order.state>=2 and t_order.userid in (".$user.") and TO_DAYS(t_order.receive_time)-TO_DAYS('".$date."')=1)
				order by t_category.export,t_product.export,productid";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	/*
	public function find_export_orderdetails($date){//
		$sql="select t_product.id,t_product.name,sum(amount) as total,t_product.unit,t_product.description from t_orderdetail,t_product 
				where t_orderdetail.productid=t_product.id 
				and t_orderdetail.orderid in (select t_order.id from t_order where t_order.state>=2 and TO_DAYS(t_order.receive_time)-TO_DAYS('".$date."')=1)
				group by productid order by t_product.categoryid";
		$query = $this->db->query($sql);
		return $query->result_array();
	}*/
	/*
	 * 根据日期得到订单详情(该方法用于成本分析)
	 */
	public function find_export_orderdetails_by_date($date){//
		$sql="select t_product.name,sum(amount) as amount,t_orderdetail.price as price,t_product.unit from t_orderdetail,t_product 
				where t_orderdetail.productid=t_product.id 
				and t_orderdetail.orderid in (select t_order.id from t_order  where t_order.state=4 and DATE_FORMAT(t_order.receive_time,'%Y-%m-%d')='".$date."')
				group by productid";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	/*
	 * 根据起始日期得到收入(该方法用于成本分析)
	 */
	public function count_orderdetails_by_date($startdate,$enddate){//
		$sql="select sum(t_orderdetail.amount*t_orderdetail.price)  as total from t_orderdetail
				where  t_orderdetail.orderid in (select t_order.id from t_order  where t_order.state=4 and t_order.receive_time>='".$startdate."' and  t_order.receive_time<='".$enddate."')";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	/*
	 * 根据起始日期和付款方式得到收入(该方法用于成本分析)
	 */
	public function count_orderdetails_by_date_payment($startdate,$enddate,$payment){//
		$sql="select sum(t_orderdetail.amount*t_orderdetail.price)  as total from t_orderdetail
				where  t_orderdetail.orderid in (select t_order.id from t_order  where t_order.state=4 and payment_mode='".$payment."' and t_order.receive_time>='".$startdate."' and  t_order.receive_time<='".$enddate."')";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	/*
	 * 根据起始日期得到订单详情(该方法用于查看库存)
	 */
	public function get_orderdetails_by_date($startdate,$enddate){//
		$sql="select productid,sum(amount) as amount from t_orderdetail
				where  t_orderdetail.orderid in (select t_order.id from t_order  where t_order.state>=2 and t_order.receive_time>='".$startdate."' and  t_order.receive_time<='".$enddate."')
				group by productid";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	/*
	 * 根据起始日期和商品ID得到订单详情(用于添加库存商品)
	 */
	public function get_orderdetail_by_date_product($productid,$startdate,$enddate){//
		$sql="select productid,sum(amount) as amount from t_orderdetail
				where  productid=".$productid." and t_orderdetail.orderid in (select t_order.id from t_order  where t_order.state>=2 and t_order.receive_time>='".$startdate."' and  t_order.receive_time<='".$enddate."')";
		$query = $this->db->query($sql);
		return $query->row_array();
	}
	/*
	 * 根据日期得到订单详情(该方法用于供应商采购单)
	 */
	public function find_supply_orderdetails_by_date($date){//
		$sql="select t_product.name,sum(amount) as amount,t_product.unit,t_product.description from t_orderdetail,t_product 
				where t_orderdetail.productid=t_product.id 
				and t_orderdetail.orderid in (select t_order.id from t_order  where t_order.state>=2 and TO_DAYS(t_order.receive_time)-TO_DAYS('".$date."')=1) and  t_orderdetail.productid in(select productid from t_goods)
				group by productid";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	/*
	 * 根据日期得到订单详情(该方法用于非供应商采购单)
	 */
	public function find_nosupply_orderdetails_by_date($date){//
		$sql="select t_product.id,t_product.name,amount,t_product.unit,t_product.price,t_product.description from t_orderdetail,t_product 
				where t_orderdetail.productid=t_product.id 
				and t_orderdetail.orderid in (select t_order.id from t_order where t_order.state>=2 and TO_DAYS(t_order.receive_time)-TO_DAYS('".$date."')=1) and  t_orderdetail.productid not in(select productid from t_goods)
				order by t_product.categoryid,t_product.export";
		$query = $this->db->query($sql);
		return $query->result_array();
	}
	/*
	 * 根据商品ID得到线上周销售量
	 */
	public function get_weeklyamount_by_product($productid){
		$this->db->select('sum(amount) as amount');
		$this->db->from('t_orderdetail');
		$this->db->where('productid',$productid);
		$this->db->where('state>=2');
		$this->db->where('del=0');
		$this->db->where("TO_DAYS(NOW())-TO_DAYS(order_time)<=7");
		$this->db->join('t_order', 't_order.id = t_orderdetail.orderid','left');
		$query = $this->db->get();
		return $query->row_array();
	}
	/*
	 * 根据商品ID得到线上月销售量
	 */
	public function get_monthlyamount_by_product($productid){
		$this->db->select('sum(amount) as amount');
		$this->db->from('t_orderdetail');
		$this->db->where('productid',$productid);
		$this->db->where('state>=2');
		$this->db->where('del=0');
		$this->db->where("TO_DAYS(NOW())-TO_DAYS(order_time)<=30");
		$this->db->join('t_order', 't_order.id = t_orderdetail.orderid','left');
		$query = $this->db->get();
		return $query->row_array();
	}
	/*
	 * 根据商品ID得到所有销售量
	 */
	public function get_all_amount_by_product($productid){
		$this->db->select('sum(amount) as amount');
		$this->db->from('t_orderdetail');
		$this->db->where('productid',$productid);
		$this->db->where('state>=2');
		$this->db->where('del=0');
		$this->db->join('t_order', 't_order.id = t_orderdetail.orderid','left');
		$query = $this->db->get();
		return $query->row_array();
	}
}